package com.oa.dao.impl;

import com.oa.dao.AttendanceDao;
import com.oa.dao.BaseDao;
import com.oa.entity.Attendance;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description TODO
 * @Author hyl
 * @Date 2022/7/5 8:57
 **/
public class AttendanceDaoImpl extends BaseDao implements AttendanceDao {
	@Override
	public List<Attendance> findAll() {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "select * from Attendance where 1=1 ";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			//5、执行
			rs = pstm.executeQuery(); //虚拟表==》查询结果的表
			//6、处理执行结果
			List<Attendance> list = new ArrayList<Attendance>();
			while(rs.next()){
				Attendance tmp = new Attendance(rs.getInt("id"), //虚拟表中的列名
						rs.getString("employeeId"),
						rs.getString("signInTime"),
						rs.getString("signOutTime"));
				list.add(tmp);
			}
			return list;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return null;
	}

	@Override
	public List<Attendance> findAll(Integer beginIndex, Integer showSize) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "select * from Attendance where 1=1 limit ?,?";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, beginIndex);
			pstm.setInt(2, showSize);
			//5、执行
			rs = pstm.executeQuery(); //虚拟表==》查询结果的表
			//6、处理执行结果
			List<Attendance> list = new ArrayList<Attendance>();
			while(rs.next()){
				Attendance tmp = new Attendance(rs.getInt("id"), //虚拟表中的列名
						rs.getString("employeeId"),
						rs.getString("signInTime"),
						rs.getString("signOutTime"));
				list.add(tmp);
			}
			return list;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return null;
	}

	@Override
	public List<Attendance> findParam(String empName, String time) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "SELECT *" +
				" from attendance a,employee e" +
				" where a.employeeId = e.id ";

		if(empName!=null && !empName.trim().equals("")){
			sql += " and e.`name` like '%"+empName+"%'";
		}
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			//5、执行
			rs = pstm.executeQuery(); //虚拟表==》查询结果的表
			//6、处理执行结果
			List<Attendance> list = new ArrayList<Attendance>();
			while(rs.next()){
				Attendance tmp = new Attendance(rs.getInt("id"), //虚拟表中的列名
						rs.getString("employeeId"),
						rs.getString("signInTime"),
						rs.getString("signOutTime"));
				list.add(tmp);
			}
			return list;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return null;
	}

	@Override
	public int add(Attendance attendance) {
		//2、创建连接对象
		Connection conn = getConnection();
		//3、编写sql
		String sql = "insert into Attendance( employeeId,  signInTime,  signOutTime) values(?,?,?) ";
		//4、创建执行对象
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, attendance.getEmployeeId());
			pstm.setString(2, attendance.getSignInTime());
			pstm.setString(3, attendance.getSignOutTime());
			//5、执行
			int ret = pstm.executeUpdate();
			//6、处理执行结果
			return ret;
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}finally {
			//7、关闭
			closeAll(rs,pstm,conn);
		}
		return 0;
	}
}
